var express = require("express");
// var uunewid = require("uuid");
var router = express.Router();
// const { checkAuth } = require("../utils/utils");
var constants = require("../utils/constants");
let log = require("cf-nodejs-logging-support");
log.setLoggingLevel("info");
var moment = require("moment");
const { ohana } = require("ohana-node-orm");
const ohana_instance = new ohana("SAP_JIANGDU_COMPANY_PORTRAITS");
const ohana_instance_user = new ohana(constants.REGISTEDUSERSTABLE);

router.get("/companyLocations", function (req, res, next) {
  // #swagger.tags = ['汇报专用']
  // #swagger.summary = '获得所有的经纬度'
  // #swagger.description = '无需参数'
  /* #swagger.security = [{
				 "JiangduJWT": []
	}] */
  /* #swagger.parameters['location'] = {
          description: "仙女镇",
          required: false,
  } */
  /* #swagger.parameters['industry'] = {
          description: "制造业",
          required: false,
  } */
  //const userNew = checkAuth(req.user, constants.ASSETAUTH);

  const { location } = req.query;
  const { industry } = req.query;
  let condition = {
    location: location,
    industry: industry,
  };
  if (!location && !industry) {
    getLocation().then((result) => {
      res.send(result);
    });
  } else {
    getDataByLocation(condition)
      .then((result) => {
        res.send(result);
      })
      .catch((err) => {
        log.error(err);
        res.status(500).send(err);
      });
  }
});
async function getLocation() {
  var result = await ohana_instance_user.find([
    "USER_ID",
    "COMPANY_NAME",
    "LONGTITUDE",
    "LATITUDE",
  ]);
  return result;
}

router.get("/companyNumberByTown", function (req, res, next) {
  // #swagger.tags = ['汇报专用']
  // #swagger.summary = '获得所有镇的数字经纬度'
  // #swagger.description = '无需参数'
  /* #swagger.security = [{
				 "JiangduJWT": []
	}] */
  //const userNew = checkAuth(req.user, constants.ASSETAUTH);

  let locations = [
    "仙女镇",
    "武坚镇",
    "小纪镇",
    "樊川镇",
    "真武镇",
    "邵伯镇",
    "丁伙镇",
    "丁沟镇",
    "郭村镇",
    "吴桥镇",
    "宜陵镇",
    "浦头镇",
    "大桥镇",
  ];
  getDataByTown(locations)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});
async function getDataByTown(locations) {
  var result = [];
  for (let index = 0; index < locations.length; index++) {
    const element = locations[index];
    let condition = {
      location: element,
    };
    number = await getDataByLocation(condition);
    result.push({
      name: element,
      number: number.length,
    });
  }
  return result;
}

router.get("/main", function (req, res, next) {
  // #swagger.tags = ['汇报专用']
  // #swagger.summary = '基础信息'
  // #swagger.description = '五大资源分布，入驻企业，注册用户数'
  /* #swagger.security = [{
				   "JiangduJWT": []
	  }] */
  //const userNew = checkAuth(req.user, constants.ASSETAUTH);
  getMain()
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function getMain() {
  var results = {
    overall: [],
    recentViews: [],
    recentAnomyViews: [],
    fiveResourceViews: [],
    // recentUserViews:[],
    policy: {},
    assets: {},
    techs: {},
    fins: {},
    talents: {},
    micro: {},
    analytics: {},
  };
  //注册用户总数
  var sqlString =
    "select count(*) as total from " + constants.REGISTEDUSERSTABLE;
  var resultNew = await ohana_instance_user.raw(sqlString);
  results.overall.push({
    name: "ALL_USERS",
    total: resultNew[0].TOTAL,
  });
  //已注册企业
  sqlString =
    "select count(*) as total from " +
    constants.REGISTEDUSERSTABLE +
    " where LEVEL!='L2' and STATUS = '" +
    constants.STATUSCONFIRMED +
    "'";
  resultNew = await ohana_instance_user.raw(sqlString);
  var all_companys = resultNew[0].TOTAL;
  results.overall.push({
    name: "ALL_COMPANYS",
    total: resultNew[0].TOTAL,
  });
  //平台访问总量
  sqlString = "select count(*) as total from " + constants.ALLLOGTABLE;
  resultNew = await ohana_instance_user.raw(sqlString);
  results.overall.push({
    name: "ALL_VIEWS",
    total: resultNew[0].TOTAL,
  });
  // 五大资源分布访问：
  await getFiveResource("设备", results.fiveResourceViews);
  await getFiveResource("政策", results.fiveResourceViews);
  await getFiveResource("人才", results.fiveResourceViews);
  await getFiveResource("科技", results.fiveResourceViews);
  await getFiveResource("金融", results.fiveResourceViews);

  //自测完成度
  var userQuestion =
    "select USER_ID,count(*) as TOTAL  from QUESTIONNAIRES where USER_ID is not NULL  group by USER_ID";
  const userResult = await ohana_instance_user.raw(userQuestion);
  var questionnaireData = {
    finished: 0,
    unfinished: 0,
  };
  userResult.map((one) => {
    if (one.TOTAL == 9) {
      questionnaireData.finished++;
    } else {
      questionnaireData.unfinished++;
    }
  });
  results.overall.push({
    name: "QUESTIONNAIRE_PERCENTAGE",
    total: questionnaireData.finished / all_companys,
  });
  //获取近三个月的
  var today = moment().format("YYYY-MM-DD");
  var todayNew = moment().add(1, "d").format("YYYY-MM-DD");
  var todayPlus = moment().subtract(3, "months").format("YYYY-MM-DD");
  // console.log(todayPlus);
  sqlString =
    "select count(*) as TOTAL ,CREATED_AT from (select TO_NVARCHAR(CREATED_AT,'YYYY-MM-DD')  as CREATED_AT from " +
    constants.ALLLOGTABLE +
    " where CREATED_AT >= '" +
    todayPlus +
    "' and CREATED_AT <= '" +
    todayNew +
    "') group by CREATED_AT order by CREATED_AT asc";
  resultNew = await ohana_instance_user.raw(sqlString);
  if (resultNew.length > 0) {
    // console.log(result[result.length - 1]);
    if (resultNew[resultNew.length - 1].CREATED_AT != today) {
      resultNew.push({ CREATED_AT: today, TOTAL: 0 });
    }
  }
  results.recentViews=resultNew;
  sqlString =
    "select count(*) as TOTAL ,CREATED_AT from (select TO_NVARCHAR(CREATED_AT,'YYYY-MM-DD')  as CREATED_AT from " +
    constants.ALLLOGTABLE +
    " where USER is null and  CREATED_AT between '" +
    todayPlus +
    "' and '" +
    todayNew +
    "') group by CREATED_AT order by CREATED_AT asc";
  resultNew = await ohana_instance_user.raw(sqlString);

  // add 0 to the end
  if (resultNew.length > 0) {
    // console.log(result[result.length - 1]);
    if (resultNew[resultNew.length - 1].CREATED_AT != today) {
      resultNew.push({ CREATED_AT: today, TOTAL: 0 });
    }
  }
  // console.log(results.recentViews)
  results.recentViews.map((one,index) => {
    var exitOrNot = false;
    resultNew.map((newOne) => {
      if (newOne.CREATED_AT == one.CREATED_AT) {
        exitOrNot = true;
      }
    });
    if(!exitOrNot)
    {
      resultNew.splice(index,0,{CREATED_AT:one.CREATED_AT,TOTAL:0});
    }
  });

  results.recentAnomyViews=resultNew;

  await getPolicyStatistics(results.policy);
  await getAssetStatistics(results.assets);
  await getTechStatistics(results.techs);
  await getFinStatistics(results.fins);
  await getTalentStatistics(results.talents);
  await getMicroStatistics(results.micro);
  await getAnalyticsStatistics(results.analytics);
  return results;
}

async function getFiveResource(type, array) {
  var sqlString = generateFive(type);
  var result = await ohana_instance_user.raw(sqlString);
  array.push({ type: type, total: result[0].TOTAL });
}
function generateFive(type) {
  var sqlString =
    " select Count(*) as total from " +
    constants.ALLLOGTABLE +
    " where TYPE like '%" +
    type +
    "%'";
  return sqlString;
}

async function getAnalyticsStatistics(obj) {
  //活跃企业行业访问量
  var sqlString =
    "select top 5 A.cap,sum(B.TOTAL) as TOTAL from " +
    constants.REGISTEDUSERSTABLE +
    " as A inner join (select USER, count(*) as TOTAL  from  " +
    constants.ALLLOGTABLE +
    " where USER is not null group by USER) as B on A.USER_ID = B.user group by A.CAP";
  // console.log(sqlString);
  var result = await ohana_instance_user.raw(sqlString);
  obj.TOP_INDUSTRY_VIEWS = result;
}
async function getMicroStatistics(obj) {
  var sqlString =
    "select TYPE ,count(*) as TOTAL from " +
    constants.ALLLOGTABLE +
    " where TYPE like '访问微%' and TYPE != '访问微服务首页' group by TYPE ";
  var result = await ohana_instance_user.raw(sqlString);
  obj.MICRO = result;
  sqlString =
    "select service, count(*) as TOTAL from " +
    constants.BTP_SERVICE_REQUEST +
    " where  STATUS = '" +
    constants.STATUSCONFIRMED +
    "'  group by service";
  result = await ohana_instance_user.raw(sqlString);
  obj.BTP = result;
}

async function getTalentStatistics(obj) {
  var sqlString = "select count(*) as total from " + constants.TALENTTABLE;
  var result = await ohana_instance_user.raw(sqlString);
  obj.TOTAL = result[0].TOTAL;
  sqlString =
    "select TYPE,COUNT(*) as TOTAL from " +
    constants.TALENTTABLE +
    " group by TYPE";
  result = await ohana_instance_user.raw(sqlString);
  obj.TYPE_TOTAL = result;

  sqlString =
    "select count(distinct research_area) as distinct_research from " +
    constants.TALENTTABLE +
    " where research_area is not null ";
  result = await ohana_instance_user.raw(sqlString);
  obj.RESEARCH_AREAS = result[0].DISTINCT_RESEARCH;

  sqlString =
    "select  top 4 research_area ,count(*) as total from " +
    constants.TALENTTABLE +
    " where research_area is not null  group by research_area order by total desc";
  result = await ohana_instance_user.raw(sqlString);
  obj.RESEARCH_AREAS_DETAIL = result;

  sqlString =
    "select count(distinct POSITION) as distinct_research from " +
    constants.TALENTTABLE +
    " where POSITION is not null and type =  '专业职场人才'";
  result = await ohana_instance_user.raw(sqlString);
  obj.GONGZHONG = result[0].DISTINCT_RESEARCH;
}
async function getTechStatistics(obj) {
  var sqlString = "select count(*) as total from " + constants.TECHTABLE;
  var result = await ohana_instance_user.raw(sqlString);
  obj.TOTAL = result[0].TOTAL;
  sqlString =
    "select TYPE,COUNT(*) as TOTAL from " +
    constants.TECHTABLE +
    " group by TYPE";
  result = await ohana_instance_user.raw(sqlString);
  obj.TYPE_TOTAL = result;
  sqlString =
    "select COUNT(distinct HW_CXY_CORP_CYLY) as TOTAL from " +
    constants.TECHTABLE +
    " where HW_CXY_CORP_CYLY is not null";
  result = await ohana_instance_user.raw(sqlString);
  obj.RESEARCH_TOTAL = result[0].TOTAL;
  sqlString =
    "select top 5 HW_CXY_CORP_CYLY, COUNT(*) as TOTAL from " +
    constants.TECHTABLE +
    " where HW_CXY_CORP_CYLY is not null group BY HW_CXY_CORP_CYLY order by TOTAL desc";
  result = await ohana_instance_user.raw(sqlString);
  obj.RESEARCH_AREAS = result;
}
async function getFinStatistics(obj) {
  var sqlString = "select count(*) as total from " + constants.FINTABLE;
  var result = await ohana_instance_user.raw(sqlString);
  obj.TOTAL = result[0].TOTAL;
  sqlString =
    "select count(distinct BANK_NAME) as TOTAL from " + constants.FINTABLE;
  result = await ohana_instance_user.raw(sqlString);
  obj.BANK_NUMBER = result[0].TOTAL;
  sqlString =
    "SELECT COUNT(*) as TOTAL from " +
    constants.ALLLOGTABLE +
    " WHERE TYPE = '金融申请链接'";
  result = await ohana_instance_user.raw(sqlString);
  obj.FIN_APPLY = result[0].TOTAL;
  sqlString =
    "select A.NAME,A.BANK_NAME,A.LOAN_QUOTA,A.RATE_LOW,A.RATE_HIGH,B.TOTAL from " +
    constants.FINTABLE +
    " as A inner join (select top 5 FAVOURITE_ID,COUNT(*) as TOTAL from " +
    constants.FAVORITETABLE +
    " where TYPE = 'FI' group by FAVOURITE_ID order by TOTAL desc) as B  on A.FIN_ID = B.FAVOURITE_ID";
  result = await ohana_instance_user.raw(sqlString);
  obj.TOP_FIN = result;

  sqlString =
    "SELECT LOAN_METHOD,count(*) AS TOTAL FROM " +
    constants.FINTABLE +
    " GROUP BY LOAN_METHOD order by TOTAL desc";
  result = await ohana_instance_user.raw(sqlString);
  obj.FIN_TYPE = result;

  sqlString =
    "select tag_name,total from " +
    constants.TAGTABLE +
    " as A inner join (select  top 5" +
    "  TAG_ID_TAG_ID,count(*) as total  from " +
    constants.TAGFINTABLE +
    " group by TAG_ID_TAG_ID order  by total desc) as B  on A.TAG_ID = B.TAG_ID_TAG_ID order  by total desc";
  result = await ohana_instance_user.raw(sqlString);
  obj.TOP_FIN_TAG = result;
}
async function getPolicyStatistics(obj) {
  var sqlString = "select count(*) as total from " + constants.POLICYTABLE;
  var result = await ohana_instance_user.raw(sqlString);
  obj.TOTAL = result[0].TOTAL;
  sqlString =
    "select count(distinct POLICY_TITLE) as TOTAL from  " +
    constants.POLICYTABLE;
  result = await ohana_instance_user.raw(sqlString);
  obj.POLICY_FILE_TOTAL = result[0].TOTAL;

  // policy type
  sqlString =
    "SELECT POLICY_TYPE,count(*) AS TOTAL FROM " +
    constants.POLICYTABLE +
    " GROUP BY POLICY_TYPE order by TOTAL desc";
  result = await ohana_instance_user.raw(sqlString);
  obj.POLICY_TYPE = result;
  /// all logs
  sqlString =
    "SELECT COUNT(*) as TOTAL from " +
    constants.ALLLOGTABLE +
    " WHERE TYPE = '政策申报委托'";
  result = await ohana_instance_user.raw(sqlString);
  obj.POLICY_APPLY = result[0].TOTAL;

  sqlString =
    "select A.POLICY_TITLE,sum(B.total) as sumtotal from  " +
    constants.POLICYTABLE +
    " as A inner join  (select RECOMMENDED_ID,count(*) as total from SAP_JIANGDU_RECOMMENDED_LIST_POLICY " +
    " group by RECOMMENDED_ID order by total desc) as B on A.POLICY_ID = B.recommended_id group by A.Policy_title order by  sumtotal desc";

  result = await ohana_instance_user.raw(sqlString);
  obj.TOP_POLICY = result;

  sqlString =
    "select tag_name,total from " +
    constants.TAGTABLE +
    " as A inner join (select  top 5" +
    "  TAG_ID_TAG_ID,count(*) as total  from " +
    constants.TAGPOLICYTABLE +
    " group by TAG_ID_TAG_ID order  by total desc) as B  on A.TAG_ID = B.TAG_ID_TAG_ID order  by total desc";
  result = await ohana_instance_user.raw(sqlString);
  obj.TOP_POLICY_TAG = result;
}

async function getAssetStatistics(obj) {
  var sqlString =
    "select top 4 type,count(*) as total from " +
    constants.ASSETTABLE +
    " where type is not null group by type order by total desc";
  var result = await ohana_instance_user.raw(sqlString);
  obj.CATEGORY = result;

  sqlString =
    "select transaction_type,count (*) as TOTAL from " +
    constants.ASSETTABLE +
    " group by transaction_type";
  result = await ohana_instance_user.raw(sqlString);
  obj.TYPE_TOTAL = result;
  // sqlString =
  //   "select count(distinct POLICY_TITLE) as TOTAL from  " +
  //   constants.POLICYTABLE;
  // result = await ohana_instance_user.raw(sqlString);
  // obj.POLICY_FILE_TOTAL = result[0].TOTAL;

  // // policy type
  // sqlString =
  //   "SELECT POLICY_TYPE,count(*) AS TOTAL FROM " +
  //   constants.POLICYTABLE +
  //   " GROUP BY POLICY_TYPE";
  // result = await ohana_instance_user.raw(sqlString);
  // obj.POLICY_TYPE = result;
}

router.get("/", function (req, res, next) {
  // #swagger.tags = ['汇报专用']
  // #swagger.summary = '五大资源分布'
  // #swagger.description = '五大资源分布，入驻企业，注册用户数'
  /* #swagger.security = [{
				 "JiangduJWT": []
	}] */
  //const userNew = checkAuth(req.user, constants.ASSETAUTH);
  getStatistics()
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function getStatistics() {
  let resultTable = [];
  let tables = [
    constants.POLICYTABLE,
    constants.ASSETTABLE,
    constants.TECHTABLE,
    constants.TALENTTABLE,
    constants.FINTABLE,
    constants.REGISTEDUSERSTABLE,
    constants.RECOMMENDLISTTABLE,
  ];
  var yiFabu = {
    table: "yifabu",
    TOTAL: 0,
  };
  for (var i = 0; i < tables.length; i++) {
    var sqlString = "select count(*) as total from " + tables[i];
    var yifabuString = sqlString;
    const result = await ohana_instance_user.raw(sqlString);
    const resultCount = result;
    resultTable.push({
      table: tables[i],
      TOTAL: result[0].TOTAL,
    });
    yiFabu.TOTAL += resultCount[0].TOTAL;
  }
  resultTable.push(yiFabu);
  return resultTable;
}

router.get("/portraits", function (req, res, next) {
  // #swagger.tags = ['汇报专用']
  // #swagger.summary = '获取企业画像'
  // #swagger.description = 'get请求需要传入USER_Id'
  /* #swagger.security = [{
                   "JiangduJWT": []
      }] */
  //const user = checkAuth(req.user, constants.BASICAUTH);

  // if (!user.authCheck) {
  //     res.sendStatus(403);
  //select company_id
  getData()
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.get("/portraitsByLocation", function (req, res, next) {
  // #swagger.tags = ['汇报专用']
  // #swagger.summary = '根据镇获取企业画像'
  // #swagger.description = 'get请求需要传入地址比如仙女镇'
  /* #swagger.security = [{
                   "JiangduJWT": []
      }] */
  /* #swagger.parameters['location'] = {
          description: "inprocess",
          required: false,
  } */
  const { location } = req.query;
  const { industry } = req.query;
  let condition = {
    location: location,
    industry: industry,
  };
  if (!location && !industry) {
    res.sendStatus(400);
    return;
  }
  getDataByLocation(condition)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function getDataByLocation(condition) {
  var sqlString =
    "select USER_ID,COMPANY_NAME,LONGTITUDE,LATITUDE from " +
    constants.REGISTEDUSERSTABLE +
    " where STATUS = '" +
    constants.STATUSCONFIRMED +
    "' ";
  var conditionString = "";
  if (condition.location) {
    conditionString =
      " AND COMPANY_ADDRESS like '%" + condition.location + "%'";
  }
  if (condition.industry) {
    conditionString += " AND INDUSTRY = '" + condition.industry + "' ";
  }

  console.log(sqlString + conditionString);
  const result = await ohana_instance.raw(sqlString + conditionString);
  return result;
}

router.get("/portraits/:userid", function (req, res, next) {
  // #swagger.tags = ['汇报专用']
  // #swagger.summary = '获取企业画像，中间大屏点击用这个'
  // #swagger.description = 'get请求需要传入USER_Id '
  /* #swagger.security = [{
                   "JiangduJWT": []
      }] */

  //const user = checkAuth(req.user, constants.BASICAUTH);

  // if (!user.authCheck) {
  //     res.sendStatus(403);
  //select company_id
  getData(req.params.userid)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.post("/portraits", function (req, res, next) {
  const { data } = req.body;
  updatePortraits(data)
    .then((result) => {
      log.info(result);
      if (result == 1) {
        res.sendStatus(200);
      } else {
        res.send(500);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.get("/services", function (req, res, next) {
  getServices()
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.get("/getLastEntries", function (req, res, next) {
  const { days } = req.query;
  if (days) {
    //const userNew = checkAuth(req.user, constants.ASSETAUTH);
    getLastEntries(days)
      .then((result) => {
        res.send(result);
      })
      .catch((err) => {
        log.error(err);
        res.status(500).send(err);
      });
  } else {
    res.send({
      message: "please add parameter",
    });
  }
});

async function getServices() {
  var sqlString =
    "SELECT SERVICE, COUNT(SERVICE) AS TOTAL FROM " +
    constants.BTP_SERVICE_REQUEST +
    " GROUP BY SERVICE";
  //const services = new ohana(constants.BTP_SERVICE_REQUEST);
  const result = await ohana_instance.raw(sqlString);
  return result;
}

async function updatePortraits(body) {
  const portraits_instance = new ohana(constants.COMPANY_PORTRAITS); // new ohana('table_name');
  const result = await portraits_instance.updateOrCreate(
    {
      COMPANY_CODE_COMPANY_CODE: body.COMPANY_CODE_COMPANY_CODE,
    },
    body
  );
  //console.log(result);
  return result;
}

async function getData(user_id) {
  if (user_id) {
    //
  } else {
    var sqlString = "select SHOW_COMPANY_ID from GLOBAL_PARAMETER";
    const company_ids = await ohana_instance.raw(sqlString);
    user_id = company_ids[0].SHOW_COMPANY_ID;
    //console.log(company_ids[0].SHOW_COMPANY_ID)
  }

  let condition = {};
  condition.COMPANY_CODE_COMPANY_CODE = user_id;
  // e.log(user_id);
  let result = await ohana_instance.find(condition);
  let company_details = await ohana_instance_user.find({
    USER_ID: user_id,
  });
  let data = {
    ...result[0],
    COMPANY_NAME: company_details[0].COMPANY_NAME,
    COMPANY_CODE: company_details[0].COMPANY_CODE,
    BUSINESS_SCOPE: company_details[0].BUSINESS_SCOPE,
    COMPANY_ADDRESS: company_details[0].COMPANY_ADDRESS,
    INDUSTRY: company_details[0].INDUSTRY,
    CAP: company_details[0].CAP,
    LEGAL_REPRESENTATIVE: company_details[0].LEGAL_REPRESENTATIVE,
    COMPANY_SIZE: company_details[0].COMPANY_SIZE,
    ESTABLISHED_DATE: company_details[0].ESTABLISHED_DATE,
    COMPANY_TYPE: company_details[0].COMPANY_TYPE,
    LEVEL: company_details[0].LEVEL,
    REGISTER_CURRENCY: company_details[0].REGISTER_CURRENCY,
  };
  sqlString =
    "select * from JIANGDU_INDUSTRY_AVG_SCORE WHERE INDUSTRY ='" +
    company_details[0].INDUSTRY +
    "'";
  var avgResult = await ohana_instance_user.raw(sqlString);
  // console.log(avgResult);
  var avgObj = {};
  if (avgResult.length > 0) {
    avgResult.map((one) => {
      switch (one.KPI) {
        case "BASIC_ABILITY":
          avgObj.BASIC_ABILITY = one.AVG_SCORE;

          break;
        case "IOT":
          avgObj.IOT = one.AVG_SCORE;

          break;
        case "TECH_INNO":
          avgObj.TECH_INNO = one.AVG_SCORE;

          break;
        case "CREDIT_RISK":
          avgObj.CREDIT_RISK = one.AVG_SCORE;

          break;
        case "JINGYING_MANAGEMENT":
          avgObj.JINGYING_MANAGEMENT = one.AVG_SCORE;

          break;
        case "CONTINOUS_DEVELOP":
          avgObj.CONTINOUS_DEVELOP = one.AVG_SCORE;

          break;
        case "OVERALL_RANKING":
          avgObj.OVERALL_RANKING = one.AVG_SCORE;

          break;

        default:
          break;
      }
    });
  }
  data.INDUSTRY_AVG = avgObj;
  // console.log(company_details);
  return data;
}

async function getLastEntries(days) {
  // console.log(days);
  let resultTable = [];
  let tables = [
    constants.ASSETTABLE,
    constants.POLICYTABLE,
    constants.TECHTABLE,
    constants.TALENTTABLE,
    constants.FINTABLE,
    constants.REGISTEDUSERSTABLE,
    constants.RECOMMENDLISTTABLE,
  ];
  const comparedata = moment()
    .subtract(parseInt(days), "d")
    .format("YYYY-MM-DD");
  // console.log(comparedata);
  for (var i = 0; i < tables.length; i++) {
    let sqlUserString = "";
    var sqlString =
      "select count(*) as TOTAL ,CREATED_AT from (select TO_NVARCHAR(CREATED_AT,'YYYY-MM-DD')  as CREATED_AT from " +
      tables[i] +
      " where " +
      sqlUserString +
      "  CREATED_AT > '" +
      comparedata +
      '\') group by "CREATED_AT" order by CREATED_AT asc';

    const result = await ohana_instance.raw(sqlString);
    /**
     *
     *
     * SELECT count(*) as TOTAL,CREATED_AT from
     *  (select TO_NVARCHAR(CREATED_AT,'YYYY-MM-DD')as CREATED_AT from "118AF16985A94C8CB7D3AF83453A6879"."SAP_JIANGDU_USERS"  where CREATED_AT > '2021-09-01')
     *  group by "CREATED_AT"
     */
    resultTable.push({
      table: tables[i],
      DETAIL: result,
    });
  }
  return resultTable;
}

module.exports = router;
